﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Linq;
using Microsoft.Data.Sqlite;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SqlDao
{
    /// <summary>
    /// SQLite Helper
    /// </summary>
    public class SqliteHelper : DbHelper
    {
        private static string dbName = "data.db";
        private string dbFile;
        public string DefaultDbFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "data", dbName);
        private string ConnStr
        {
            get { return SqliteConnStringBuilder.GetConnString(dbFile); }     
        }
        private static SqliteHelper _instance;

        /// <summary>
        /// Get Instance
        /// </summary>
        public static SqliteHelper Instance
        {
            get
            {
                if (_instance == null) _instance = new SqliteHelper();
                return _instance;
            }
        }

        private SqliteHelper(string sqliteFile = "")
        {
            if (!string.IsNullOrEmpty(sqliteFile))
            {                
                this.dbFile = sqliteFile;
            }
            else
            {
                this.dbFile = DefaultDbFile;
            }
        }

        public bool Connection()
        {
            bool res = false;
            //db file is not exist,
            using (SqliteConnection connection = new SqliteConnection(ConnStr))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                    res = connection.State == ConnectionState.Open;
                }
            }

            return res;
        }


        /// <summary>  
        /// 查询  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <param name="parameters">所需参数</param>  
        /// <returns>结果DataTable</returns>  
        public DataTable ExcuteDataTable(string sql, SqliteParameter[] parameters)
        {
            using (SqliteConnection conn = new SqliteConnection(ConnStr))
            {
                conn.Open();
                using (SqliteCommand command = new SqliteCommand(sql, conn))
                {
                    PrintSql(sql);
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    var dt = new DataTable();
                    dt.Columns.Add("Cid", typeof(int));
                    dt.Columns.Add("Name", typeof(string));
                    dt.Columns.Add("Type", typeof(string));
                    dt.Columns.Add("Notnull", typeof(int));
                    dt.Columns.Add("Pk", typeof(int));
                    SqliteDataReader reader = command.ExecuteReader();
                    if (reader != null)
                    {
                        if (sql.Contains("PRAGMA table_info") || sql.StartsWith("PRAGMA"))
                        {
                            while (reader.Read())
                            {
                                DataRow dr = dt.NewRow();
                                //var column = reader.GetColumnSchema();

                                int cid = reader.GetInt32(0);
                                string name = reader.GetString(1);
                                string type = reader.GetString(2);
                                int notnull = reader.GetInt32(3);
                                // 4 不要
                                int pk = reader.GetInt32(5);

                                //dr["cid"] = column[0].ColumnOrdinal;
                             
                                dr["cid"] = cid;
                                dr["name"] = name;
                                dr["type"] = type;
                                dr["notnull"] = notnull;
                                dr["pk"] = pk;
                                dt.Rows.Add(dr);
                            }
                        }
                        else
                        {
                            dt.Load(reader);
                        }
                    }
                    reader.Close();
                    return dt;
                }
            }
        }
        /// <summary>  
        /// 查询  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <returns>结果DataTable</returns>  
        public override DataTable ExcuteDataTable(string sql)
        {
            using (SqliteConnection conn = new SqliteConnection(ConnStr))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                using (SqliteCommand command = conn.CreateCommand())
                {
                    DataTable dt = new DataTable();
                    command.CommandText = sql;
                    command.CommandTimeout = 60;
                    PrintSql(sql);
                    var reader = command.ExecuteReader();
                    dt.Load(reader);

                    return dt;
                }
            }
        }

        #region 增删改  

        /// <summary>  
        /// 增删改  
        /// </summary>  
        /// <param name="sql">要执行的sql语句</param>  
        /// <param name="parametes">所需参数</param>  
        /// <returns>所受影响的行数</returns>  
        public int ExecuteNonQuery(string sql, SqliteParameter[] parametes)
        {
            int affectedRows = 0;
            using (SqliteConnection conn = new SqliteConnection(ConnStr))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                DbTransaction transation = conn.BeginTransaction();
                try
                {

                    using (SqliteCommand command = conn.CreateCommand())
                    {
                        command.CommandText = sql;
                        command.CommandTimeout = 60;
                        PrintSql(sql);
                        if (parametes != null)
                        {
                            command.Parameters.AddRange(parametes);
                        }
                        affectedRows = command.ExecuteNonQuery();
                        transation.Commit();
                    }
                }

                catch (Exception e)
                {
                    transation.Rollback();
                    throw e;
                }
                finally
                {
                    //  Connection?.Close();
                }
            }
            return affectedRows;
        }
        /// <summary>
        /// 事务处理多条多条操作
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns></returns>
        public override int TransactionExecute(params string[] sqls)
        {
            int affectedRows = 0;
            using (var conn = new SqliteConnection(ConnStr))
            {
                try
                {
                    if (conn.State != ConnectionState.Open) conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                DbTransaction transation = conn.BeginTransaction();
                try
                {
                    using (SqliteCommand command = conn.CreateCommand())
                    {
                        //command.Connection = Connection;                 
                        for (int i = 0; i < sqls.Length; i++)
                        {
                            PrintSql(sqls[i]);
                            command.CommandText = sqls[i];
                            affectedRows += command.ExecuteNonQuery();
                        }
                        transation.Commit();
                    }
                }
                catch (Exception)
                {
                    affectedRows = 0;
                    transation.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return affectedRows;
        }


        /// <summary>
        /// 执行删除语句
        /// </summary>
        /// <param name="sql">删除语句</param>
        /// <returns>影响行数</returns>
        public override int Delete(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 删除对像 ，支持软件删除
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <param name="isTrueDelete">是否真的删除 默认软删除</param>
        /// <returns>影响行数</returns>
        public override int Delete<T>(T obj, Boolean isTrueDelete = true)
        {
            string deleteSql = SqlBuilder.GetDeleteSql(obj, isTrueDelete);
            return ExecuteNonQuery(deleteSql, null);
        }

        /// <summary>
        /// 执行修改语句
        /// </summary>
        /// <param name="sql">删除语句</param>
        /// <returns>影响行数</returns>
        public override int Update(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 修改对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public override int Update<T>(T obj)
        {
            string sql = SqlBuilder.GetUpdateSql(obj);
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 执行插入语句
        /// </summary>
        /// <param name="sql">插入语句</param>
        /// <returns>影响行数</returns>
        public override int Insert(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        /// <summary>
        /// 插入对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public override int Insert<T>(T obj)
        {
            string insertSql = SqlBuilder.GetInsertSql(obj);
            return ExecuteNonQuery(insertSql, null);
        }

        /// <summary>
        /// 插入或者更新对像
        /// </summary>
        /// <typeparam name="T">对像类型</typeparam>
        /// <param name="obj">对像</param>
        /// <returns>影响行数</returns>
        public override int InsertOrUpdate<T>(T obj)
        {
            if (CheckExist(obj))
            {
                return this.Update(obj);
            }
            else
            {
                return this.Insert(obj);
            }
        }

        #endregion
        /// <summary>
        /// 检查对像是已经存在于数据库中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public override bool CheckExist<T>(T obj)
        {
            Type type = typeof(T);
            try
            {
                PropertyInfo propertyinfo = type.GetProperty("id");
                if (propertyinfo == null)
                {
                    propertyinfo = type.GetProperty("Id");
                    if (propertyinfo == null)
                    {
                        throw new Exception(SqlBuilder.buildSqlErrorMessage);
                    }
                }
                object tempObj = propertyinfo.GetValue(obj, null);
                if (tempObj == null || tempObj.ToString().Length <= 0)
                {
                    throw new Exception(SqlBuilder.buildSqlErrorMessage);
                }
                string condition = SqlBuilder.splitChar + "id" + SqlBuilder.splitChar + "=" + SqlBuilder.valueSplitChar + tempObj.ToString() + SqlBuilder.valueSplitChar;
                string sql = SqlBuilder.GetSelectSql(SqlBuilder.GetTableName(obj), null, condition);
                DataTable dt = this.ExcuteDataTable(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                throw;
            }
        }


        /// <summary>
        /// 按SQl语句查询 
        /// </summary>
        /// <typeparam name="T"> 单个实体类 如 Car </typeparam>
        /// <param name="sql"></param>
        /// <returns>DataTable</returns>
        public override List<T> Select<T>(string sql)
        {
            DataTable dt = ExcuteDataTable(sql);
            Type type = typeof(T);
            List<T> list = new List<T>();
            FieldInfo[] pArray = type.GetFields();
            foreach (DataRow row in dt.Rows)
            {
                T t = ParseEntityWithLog<T>(row, pArray);
                list.Add(t);
            }
            dt?.Dispose();
            return list;
        }

        public override bool IsConnecting()
        {
            return true;
        }

        public override bool IsOpened()
        {
            return false;
        }

        /// <summary>
        /// 统计条数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public override int Count(string tableName, string condition)
        {
            string sql = $"SELECT count(*)  as num from  `{tableName}`";
            DataTable dt = ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                var row = dt.Rows[0];
                int res = Convert.ToInt32(row["num"]);
                return res;
            }
            return 0;
        }


        public override T FindById<T>(int id)
        {
            object t = Activator.CreateInstance(typeof(T));
            string tableName = StringHelper.CamelCaseToDBnameing(t.GetType().Name);
            String sql = SqlBuilder.GetSelectSql(tableName, null, "id = " + id);
            return Find<T>(sql);
        }

        public override T Find<T>(string sql)
        {
            DataTable dt = ExcuteDataTable(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            var row = dt.Rows[0];
            return ParseEntityWithLog<T>(row);
        }

        public override bool CheckConn(string connstring)
        {
            if (String.IsNullOrEmpty(connstring))
            {
                connstring = connectionString;
            }
            using (SqliteConnection conn = new SqliteConnection(connstring))
            {
                try
                {
                    conn.Open();
                    return conn.State == ConnectionState.Open;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// judge the table is or not exist
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public override bool ExistTable(string dbName, string table)
        {
            if (string.IsNullOrEmpty(table)) return false;
            string sql = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{table}';";
            DataTable dt = this.ExcuteDataTable(sql, null);
            return dt.Rows.Count > 0;
        }


        public override DataTable GetAllTable(string dbbame)
        {

            string sql = $"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;";
            DataTable dt = this.ExcuteDataTable(sql, null);
            return dt;
        }

        public override string[] GetAllTableName(string dbname)
        {
            string sql = $"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;";
            DataTable dt = this.ExcuteDataTable(sql, null);

            List<string> list = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                if (!((dr["name"]?.ToString() ?? "_old_").Contains("_old_")))
                {
                    list.Add(dr["name"].ToString());
                }
            }
            return list.ToArray();
        }

        /// <summary>
        /// 获取库中所有表的结构 List SqliteColumnSchema
        /// </summary>
        /// <typeparam name="T"> List SqliteColumnSchema </typeparam>
        /// <param name="dbname"> main </param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public override List<T> GetAllTableSchema<T>(string dbname)
        {
            if (typeof(T) != typeof(SqliteTableSchema) && typeof(T) != typeof(SqliteColumnSchema))
            {
                //T的类型必须是SqliteTableSchema or SqliteColumnSchema类型
                throw new Exception("type of T must is type of SqliteTableSchema or SqliteColumnSchema");
            }
            var names = GetAllTableName(dbname);
            List<T> dss = new List<T>();
            if (names.Length <= 0) return dss;
            foreach (var name in names)
            {
                string sql = $"PRAGMA table_info({name});";
                DataTable dt = this.ExcuteDataTable(sql, null);
                string json =dt.Rows.Count >0? JsonHelper.ObjectToJson(dt):"";
                if (!string.IsNullOrEmpty(json))
                {
                    var item = (T) JsonHelper.JsonToObject(json, typeof(T));       
                    dss.Add(item);
                }
            }
            return dss;
        }

        public override string GetCreateSql(string tableName)
        {
            if (string.IsNullOrEmpty(tableName)) return "";
            string sql = $"SELECT sql FROM sqlite_master WHERE type='table' AND name='{tableName}';";
            DataTable dt = this.ExcuteDataTable(sql, null);
            return dt?.Rows?[0]?["sql"]?.ToString() ?? "";
        }

        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <typeparam name="T"> SqliteColumnSchema </typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public override List<T> GetTableSchema<T>(string tableName)
        {
            if (typeof(T) != typeof(SqliteTableSchema) && typeof(T) != typeof(SqliteColumnSchema))
            {
                //T的类型必须是SqliteTableSchema or SqliteColumnSchema类型
                throw new Exception("type of T must is type of SqliteTableSchema Or SqliteColumnSchema");
            }
            string sql = $"PRAGMA table_info(`{tableName}`);";
            DataTable dt = this.ExcuteDataTable(sql, null);
            string json = JsonHelper.ObjectToJson(dt);
            var res = (List<T>)JsonHelper.JsonToObject(json, typeof(List<T>));
            return res;
        }

        public override int ExecuteNonQuery(string sql)
        {
            using (SqliteConnection conn = new SqliteConnection(ConnStr))
            {
                conn.Open();
                using (SqliteCommand command = new SqliteCommand(sql, conn))
                {
                    PrintSql(sql);
                    return command.ExecuteNonQuery();
                }
            }
        }

        [Obsolete("Sqlite con't judge Database exist", true)]
        public override bool ExistDatabase(string databaseName)
        {
            throw new NotImplementedException("Sqlite con't judge Database exist");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="file">path and file name</param>
        /// <returns></returns>
        [Obsolete("Sqlite con't Create Database", false)]
        public override bool CreateDatabase(string file)
        {
            string connStr = SqliteConnStringBuilder.GetConnString(file);

            using (SqliteConnection conn = new SqliteConnection(connStr))
            {
                // 如果文件不存在，这里会创建它  
                conn.Open();
                return true;
            }
        }

        //GetTableSchema <SqliteColumnSchema>(tablename);
        public override List<T> GetTableColumnSchema<T>(string dbname, string tablename)
        {
            return new List<T>();
        }
    }
}